/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nxlv2.nomad.accessdb.dao.jdbc;

import com.nxlv2.nomad.accessdb.dao.DAOException;
import com.nxlv2.nomad.accessdb.dao.IContactDao;
import com.nxlv2.nomad.accessdb.dao.IncoherentResultException;
import com.nxlv2.nomad.accessdb.dao.NoResultException;
import com.nxlv2.nomad.businessobjects.Contact;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Nastasia
 */
public class JdbcContactDao implements IContactDao {

    private static final Logger logger = LoggerFactory.getLogger(JdbcContactDao.class);
    private static final ConnectionManager manager = new ConnectionManager();

    /**
     * 
     * @param contact
     * @throws DAOException 
     */
    @Override
    public void saveContact(Contact contact) throws DAOException {
        if (null == contact) {
            throw new IllegalArgumentException("Can not save a null object.");
        }
        StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO contact (idBase, nom, prenom, mail, tel, defaut, dateCreation) ");
        builder.append("VALUES (?,AES_ENCRYPT(?,?),AES_ENCRYPT(?,?),AES_ENCRYPT(?,?),AES_ENCRYPT(?,?),?,NOW())");

        String request = builder.toString();
        logger.debug("Insert contact: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request, PreparedStatement.RETURN_GENERATED_KEYS);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, contact.getIdBase());
            ps.setString(2, contact.getNom());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, contact.getPrenom());
            ps.setString(5, manager.getKeyAES());
            ps.setString(6, contact.getMail());
            ps.setString(7, manager.getKeyAES());
            ps.setString(8, contact.getTel());
            ps.setString(9, manager.getKeyAES());
            ps.setBoolean(10, contact.isDefaut());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }

        try {
            ResultSet rs = ps.getGeneratedKeys();
            int pk = 0;

            while (rs.next()) {
                pk = rs.getInt(1);
            }
            contact.setIdAppli(pk);
        } catch (SQLException e) {
            logger.error("Can not execute insert in db.");
            throw new DAOException("Can not execute insert in db.", e);
        }
        manager.fermerConnection();
    }

    /**
     * 
     * @param id
     * @throws DAOException 
     */
    @Override
    public void deleteContact(int id) throws DAOException {
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("DELETE FROM contact WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Delete contact: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not delete contact with id.", e);
            throw new DAOException("Can not delete contact with id.", e);
        }
        manager.fermerConnection();
    }

    /**
     * 
     * @param contact
     * @throws DAOException 
     */
    @Override
    public void updateContact(Contact contact) throws DAOException {
        if (null == contact) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE contact ");
        builder.append("SET idBase = ?, "
                + "nom = AES_ENCRYPT(?,?),"
                + "prenom = AES_ENCRYPT(?,?),"
                + "mail = AES_ENCRYPT(?,?),"
                + "tel = AES_ENCRYPT(?,?), "
                + "defaut = ?, "
                + "dateModification = NOW() ");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Update contact: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, contact.getIdBase());
            ps.setString(2, contact.getNom());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, contact.getPrenom());
            ps.setString(5, manager.getKeyAES());
            ps.setString(6, contact.getMail());
            ps.setString(7, manager.getKeyAES());
            ps.setString(8, contact.getTel());
            ps.setString(9, manager.getKeyAES());
            ps.setBoolean(10, contact.isDefaut());
            ps.setInt(11, contact.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }
        manager.fermerConnection();
    }

    /**
     * 
     * @param id
     * @return Contact
     * @throws DAOException 
     */
    @Override
    public Contact findContactById(int id) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT contact.idBase, contact.idAppli,"
                + "AES_DECRYPT(contact.nom,?) AS nom,"
                + "AES_DECRYPT(contact.prenom,?) AS prenom,"
                + "AES_DECRYPT(contact.mail,?) AS mail,"
                + "AES_DECRYPT(contact.tel,?) AS tel, defaut ");
        builder.append("FROM contact "
                + "WHERE contact.idAppli = ? ");

        String request = builder.toString();
        logger.debug("select contact: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, manager.getKeyAES());
            ps.setInt(5, id);
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Contact> contacts = null;
        try {
            contacts = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == contacts || contacts.isEmpty()) {
            throw new NoResultException("No contact found with id " + id);
        }
        if (contacts.size() > 1) {
            throw new IncoherentResultException("Many records with a same id.");
        }
        manager.fermerConnection();
        return contacts.get(0);

    }

    /**
     *
     * @param rst
     * @return List<Contact>
     * @throws SQLException
     */
    private List<Contact> map(ResultSet rst) throws SQLException {
        List<Contact> contacts = new ArrayList<>();

        while (rst.next()) {

            Contact contact = new Contact();
            contact.setIdAppli(rst.getInt("idAppli"));
            contact.setIdBase(rst.getInt("idBase"));
            contact.setNom(rst.getString("nom"));
            contact.setPrenom(rst.getString("prenom"));
            contact.setMail(rst.getString("mail"));
            contact.setTel(rst.getString("tel"));
            contact.setDefaut(rst.getBoolean("defaut"));
            contacts.add(contact);
        }
        return contacts;
    }
}
